﻿using System;
using System.Data;
using System.Data.SqlClient;
using Bank.DataSets;
using System.Configuration;

namespace Bank.DataAccess
{
    public class CustomerData
    {
        private SqlConnection conn;
        private SqlCommand cmd;
        private SqlDataAdapter adapter;

        public CustomerData()
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["BankSys"].ConnectionString; //"server=localhost;database=BankDB;user id=sa;password=sa123";
            adapter = new SqlDataAdapter();
            adapter.SelectCommand = GetSelectCommand();
            adapter.InsertCommand = GetInsertCommand();
            adapter.UpdateCommand = GetUpdateCommand();
            adapter.DeleteCommand = GetDeleteCommand();
        }

        private SqlCommand GetSelectCommand()
        {
            cmd = new SqlCommand();
            cmd.CommandText = "select * from customer";
            cmd.Connection = conn;
            return cmd;
        }
        private SqlCommand GetInsertCommand()
        {
            cmd = new SqlCommand();
            cmd.CommandText = "insert into Customer(CustomerName,BalanceAmt,BankAcNo) values(@CustomerName,@BalanceAmt,@BankAcNo)";
            cmd.Parameters.Add("@CustomerName", SqlDbType.Text, 150, "CustomerName");
            cmd.Parameters.Add("@BalanceAmt", SqlDbType.Decimal, 18, "BalanceAmt");
            cmd.Parameters.Add("@BankAcNo", SqlDbType.Text, 250, "BankAcNo");
            cmd.Connection = conn;
            return cmd;
        }
        private SqlCommand GetUpdateCommand()
        {
            cmd = new SqlCommand();
            cmd.CommandText = "update Customer set CustomerName=@CustomerName,BalanceAmt=@BalanceAmt,BankAcNo=@BankAcNo";
            cmd.Parameters.Add("@CustomerName", SqlDbType.Text, 150, "CustomerName");
            cmd.Parameters.Add("@BalanceAmt", SqlDbType.Decimal, 18, "BalanceAmt");
            cmd.Parameters.Add("@BankAcNo", SqlDbType.Text, 250, "BankAcNo");
            cmd.Connection = conn;
            return cmd;
        }
        private SqlCommand GetDeleteCommand()
        {
            cmd = new SqlCommand();
            cmd.CommandText = "delete from Customer where BankAcNo=@BankAcNo";
            cmd.Parameters.Add("@BankAcNo", SqlDbType.Text, 250, "BankAcNo");
            cmd.Connection = conn;
            return cmd;
        }

        public DsCustomer GetCustomer()
        {
            DsCustomer dsCust = new DsCustomer();
            adapter.Fill(dsCust, dsCust.Customer.TableName); //select
            return dsCust;
        }
        public bool UpdateToDataBase(DsCustomer dsCust) //this is for insert, update and delete
        {
            try
            {
                conn.Open();
                adapter.Update(dsCust, dsCust.Customer.TableName); //update/delete/insert
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }
        
        public int CheckUser(string bankAcNo)
        {
            cmd = new SqlCommand();
            cmd.CommandText = "select count(*) from customer where BankAcNo='" + bankAcNo + "'";
            cmd.Connection = conn;
           conn.Open();
            try
            {
                int rows = Convert.ToInt32(cmd.ExecuteScalar());
                return rows;
            }
            finally
            {
                conn.Close();
            }
            
        }
    }
}
